﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using KPIReport.Library;
using KPIReport.Library.DomainObject.Administration.Catalog;
using KPIReport.Library.Enum;
using KPIReport.Library.Interfaces;

namespace KPIReport.Library.DataAccess.Administration.Catalog
{
    public partial class transaksihdrRepo
    {
        public bool SaveUpload(List<transaksihdr> domains)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    domains.ForEach(delegate(transaksihdr domain)
                    {
//                        SqlCommand cmd = conn.CreateCommand();
//                        cmd.CommandText = @"INSERT INTO dbo.transaksihdr([Customer Code],[Customer Name],[Customer Type], [Salesman Code],[Salesman Name],[Sales Order Number],
//                        [Warehouse Code],Status,[Invoice Date],[Due Date],[Total Amount],[Total Amount after Customer Discount],[Tax Amount],[Total Net Amount],[invoice number])
//                        VALUES(@Customer_Code, @Customer_Name, @Customer_Type, @Salesman_Code, @Salesman_Name,@Sales_Order_Number, @Warehouse_Code, @Status, @Invoice_Date
//                        , @Due_Date,  @Total_Amount, @Total_Amount_after_Customer_Discount, @Tax_Amount, @Total_Net_Amount, @Invoice_Number)";
                        
//                        cmd.CommandType = CommandType.Text;

//                        //cmd.CommandText = "INSERT INTO dbo.transaksihdr(Customer Code, Customer Name, Customer Type, Salesman Code, Salesman Name, Invoice Number, Sales Order Number, Warehouse Code, Status, Invoice Date, Due Date, Customer Area Code, Total Amount, Customer Discount Percentage, Customer Discount Amount, Total Amount after Customer Discount, Tax Amount, Total Net Amount, Remark, Created By) VALUES(@Customer Code, @Customer Name, @Customer Type, @Salesman Code, @Salesman Name, @Invoice Number, @Sales Order Number, @Warehouse Code, @Status, @Invoice Date, @Due Date, @Customer Area Code, @Total Amount, @Customer Discount Percentage, @Customer Discount Amount, @Total Amount after Customer Discount, @Tax Amount, @Total Net Amount, @Remark, @Created By)";
//                        //cmd.CommandText = "SP_transaksihdr_Insert";
//                        //cmd.CommandType = CommandType.StoredProcedure;
                        
//                        cmd.Parameters.Add("@Customer_Code", SqlDbType.NVarChar).Value = (object)domain.Customer_Code ?? DBNull.Value;
//                        cmd.Parameters.Add("@Customer_Name", SqlDbType.NVarChar).Value = (object)domain.Customer_Name ?? DBNull.Value;
//                        cmd.Parameters.Add("@Customer_Type", SqlDbType.NVarChar).Value = (object)domain.Customer_Type ?? DBNull.Value;
//                        cmd.Parameters.Add("@Salesman_Code", SqlDbType.NVarChar).Value = (object)domain.Salesman_Code ?? DBNull.Value;
//                        cmd.Parameters.Add("@Salesman_Name", SqlDbType.NVarChar).Value = (object)domain.Salesman_Name ?? DBNull.Value;
                        
                        
//                        cmd.Parameters.Add("@Sales_Order_Number", SqlDbType.NVarChar).Value = (object)domain.Sales_Order_Number ?? DBNull.Value;
//                        cmd.Parameters.Add("@Warehouse_Code", SqlDbType.NVarChar).Value = (object)domain.Warehouse_Code ?? DBNull.Value;
//                        cmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = (object)domain.Status ?? DBNull.Value;
//                        cmd.Parameters.Add("@Invoice_Date", SqlDbType.DateTime).Value = (object)domain.Invoice_Date ?? DBNull.Value;
//                        cmd.Parameters.Add("@Due_Date", SqlDbType.DateTime).Value = (object)domain.Due_Date ?? DBNull.Value;
//                        //cmd.Parameters.Add("@Customer_Area_Code", SqlDbType.NVarChar).Value = (object)domain.Customer_Area_Code ?? DBNull.Value;
//                        cmd.Parameters.Add("@Total_Amount", SqlDbType.Float).Value = (object)domain.Total_Amount ?? DBNull.Value;
//                        //cmd.Parameters.Add("@Customer_Discount_Percentage", SqlDbType.Float).Value = (object)domain.Customer_Discount_Percentage ?? DBNull.Value;
//                        //cmd.Parameters.Add("@Customer_Discount_Amount", SqlDbType.Float).Value = (object)domain.Customer_Discount_Amount ?? DBNull.Value;
//                        cmd.Parameters.Add("@Total_Amount_after_Customer_Discount", SqlDbType.Float).Value = (object)domain.Total_Amount_after_Customer_Discount ?? DBNull.Value;
//                        cmd.Parameters.Add("@Tax_Amount", SqlDbType.Float).Value = (object)domain.Tax_Amount ?? DBNull.Value;
//                        cmd.Parameters.Add("@Total_Net_Amount", SqlDbType.Float).Value = (object)domain.Total_Net_Amount ?? DBNull.Value;
//                        ///cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = (object)domain.Remark ?? DBNull.Value;
//                        //cmd.Parameters.Add("@Created_By", SqlDbType.NVarChar).Value = (object)domain.Created_By ?? DBNull.Value;
//                         cmd.Parameters.Add("@Invoice_Number", SqlDbType.NVarChar).Value = (object)domain.Invoice_Number ?? DBNull.Value;

                        SqlCommand cmd = conn.CreateCommand();
                        cmd.CommandText = @"INSERT INTO dbo.transaksihdr_temp([Customer Code], [Customer Name], [Customer Type], [Salesman Code],
                        [Salesman Name], [Invoice Number], [Sales Order Number], [Warehouse Code], [Status], [Invoice Date], [Due Date], [Customer Area Code], [Total Amount],
                        [Customer Discount Percentage], [Customer Discount Amount], [Total Amount after Customer Discount], [Tax Amount], [Total Net Amount], [Remark], [Created By])
                       
                        VALUES(@Customer_Code, @Customer_Name, @Customer_Type, @Salesman_Code, @Salesman_Name, @Invoice_Number, @Sales_Order_Number,
                        @Warehouse_Code, @Status, @Invoice_Date, @Due_Date, @Customer_Area_Code, @Total_Amount, @Customer_Discount_Percentage, 
                        @Customer_Discount_Amount, @Total_Amount_after_Customer_Discount, @Tax_Amount, @Total_Net_Amount, @Remark, @Created_By)";
                        cmd.CommandType = CommandType.Text;

                        //cmd.CommandText = "INSERT INTO dbo.transaksihdr(Customer Code, Customer Name, Customer Type, Salesman Code, Salesman Name, Invoice Number, Sales Order Number, Warehouse Code, Status, Invoice Date, Due Date, Customer Area Code, Total Amount, Customer Discount Percentage, Customer Discount Amount, Total Amount after Customer Discount, Tax Amount, Total Net Amount, Remark, Created By) VALUES(@Customer Code, @Customer Name, @Customer Type, @Salesman Code, @Salesman Name, @Invoice Number, @Sales Order Number, @Warehouse Code, @Status, @Invoice Date, @Due Date, @Customer Area Code, @Total Amount, @Customer Discount Percentage, @Customer Discount Amount, @Total Amount after Customer Discount, @Tax Amount, @Total Net Amount, @Remark, @Created By)";
                        //cmd.CommandText = "SP_transaksihdr_Insert";
                        //cmd.CommandType = CommandType.StoredProcedure;
                        
                        cmd.Parameters.Add("@Customer_Code", SqlDbType.NVarChar).Value = (object)domain.Customer_Code ?? DBNull.Value;
                        cmd.Parameters.Add("@Customer_Name", SqlDbType.NVarChar).Value = (object)domain.Customer_Name ?? DBNull.Value;
                        cmd.Parameters.Add("@Customer_Type", SqlDbType.NVarChar).Value = (object)domain.Customer_Type ?? DBNull.Value;
                        cmd.Parameters.Add("@Salesman_Code", SqlDbType.NVarChar).Value = (object)domain.Salesman_Code ?? DBNull.Value;
                        cmd.Parameters.Add("@Salesman_Name", SqlDbType.NVarChar).Value = (object)domain.Salesman_Name ?? DBNull.Value;
                        cmd.Parameters.Add("@Invoice_Number", SqlDbType.NVarChar).Value = (object)domain.Invoice_Number ?? DBNull.Value;
                        cmd.Parameters.Add("@Sales_Order_Number", SqlDbType.NVarChar).Value = (object)domain.Sales_Order_Number ?? DBNull.Value;
                        cmd.Parameters.Add("@Warehouse_Code", SqlDbType.NVarChar).Value = (object)domain.Warehouse_Code ?? DBNull.Value;
                        cmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = (object)domain.Status ?? DBNull.Value;
                        cmd.Parameters.Add("@Invoice_Date", SqlDbType.DateTime).Value = (object)domain.Invoice_Date ?? DBNull.Value;
                        cmd.Parameters.Add("@Due_Date", SqlDbType.DateTime).Value = (object)domain.Due_Date ?? DBNull.Value;
                        cmd.Parameters.Add("@Customer_Area_Code", SqlDbType.NVarChar).Value = (object)domain.Customer_Area_Code ?? DBNull.Value;
                        cmd.Parameters.Add("@Total_Amount", SqlDbType.Float).Value = (object)domain.Total_Amount ?? DBNull.Value;
                        cmd.Parameters.Add("@Customer_Discount_Percentage", SqlDbType.Float).Value = (object)domain.Customer_Discount_Percentage ?? DBNull.Value;
                        cmd.Parameters.Add("@Customer_Discount_Amount", SqlDbType.Float).Value = (object)domain.Customer_Discount_Amount ?? DBNull.Value;
                        cmd.Parameters.Add("@Total_Amount_after_Customer_Discount", SqlDbType.Float).Value = (object)domain.Total_Amount_after_Customer_Discount ?? DBNull.Value;
                        cmd.Parameters.Add("@Tax_Amount", SqlDbType.Float).Value = (object)domain.Tax_Amount ?? DBNull.Value;
                        cmd.Parameters.Add("@Total_Net_Amount", SqlDbType.Float).Value = (object)domain.Total_Net_Amount ?? DBNull.Value;
                        cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = (object)domain.Remark ?? DBNull.Value;
                        cmd.Parameters.Add("@Created_By", SqlDbType.NVarChar).Value = (object)domain.Created_By ?? DBNull.Value;

                        cmd.Transaction = txn;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    });


                    SqlCommand cmdDelete = conn.CreateCommand();
                    cmdDelete.CommandText = @"DELETE transaksihdr WHERE [Invoice Number] IN (SELECT [Invoice Number] FROM transaksihdr_temp)";
                    cmdDelete.CommandType = CommandType.Text;

                    cmdDelete.Transaction = txn;
                    cmdDelete.ExecuteNonQuery();
                    cmdDelete.Dispose();


                    SqlCommand cmdInsertHdr = conn.CreateCommand();
                    //cmdInsertHdr.CommandText = @"
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("insert into transaksihdr ([Customer Code],[Customer Name],[Customer Type], [Salesman Code],");
                    sb.AppendLine("[Salesman Name],[Sales Order Number],[Warehouse Code],Status,[Invoice Date],[Due Date],[Total Amount],");
                    sb.AppendLine("[Total Amount after Customer Discount],[Tax Amount],[Total Net Amount],[invoice number])");
                    sb.AppendLine("select [Customer Code],[Customer Name],[Customer Type], [Salesman Code],[Salesman Name],[Sales Order Number],");
                    sb.AppendLine("[Warehouse Code],Status,[Invoice Date],[Due Date],[Total Amount],[Total Amount after Customer Discount],[Tax Amount],");
                    sb.AppendLine("[Total Net Amount],[invoice number] ");
                    sb.AppendLine("from transaksihdr_temp");
                    sb.AppendLine("where YEAR([invoice date]) in (select distinct YEAR([invoice date]) from transaksihdr_temp) and MONTH([Invoice Date]) in(select distinct MONTH([Invoice Date]) from transaksihdr_temp)");

                    cmdInsertHdr.CommandText = sb.ToString();
                    cmdInsertHdr.CommandType = CommandType.Text;
                    cmdInsertHdr.Transaction = txn;
                    cmdInsertHdr.ExecuteNonQuery();
                    cmdInsertHdr.Dispose();


                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }


            }
            return result;
        }

        public List<transaksihdr> GetList(int pageSize, int currentPage, string sortName, string sortOrder,string parameters)
        {
            List<transaksihdr> tt = new List<transaksihdr>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);


					//cmd.CommandText = "SP_transaksihdr_GridPage";
                    ////cmd.Parameters.Add("@startRow", SqlDbType.Int).Value = startRow;
                    ////cmd.Parameters.Add("@endRow", SqlDbType.Int).Value = endRow;
                    ////cmd.Parameters.Add("@sortName", SqlDbType.VarChar).Value = sortName;
                    ////cmd.Parameters.Add("@sortOrder", SqlDbType.VarChar).Value = sortOrder;
                    ////cmd.Parameters.Add("@param", SqlDbType.VarChar).Value = parameters;

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      [Salesman Code], [Salesman Name]");
                    sbQuery.AppendFormat("    FROM ");
					sbQuery.AppendFormat("      dbo.transaksihdr ");
                    sbQuery.AppendFormat(" WHERE [Salesman Code] IS NOT NULL AND [Salesman Name] IS NOT NULL {0}", parameters);
                    sbQuery.AppendFormat(" GROUP BY [Salesman Code],[Salesman Name]");
					sbQuery.AppendFormat(" ) ");
					sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
					//cmd.CommandType = CommandType.StoredProcedure;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                transaksihdr t = new transaksihdr();
                                t.Salesman_Code = dr[transaksihdr.Fields.Salesman_Code] == DBNull.Value ? string.Empty : dr[transaksihdr.Fields.Salesman_Code].ToString();
                                t.Salesman_Name = dr[transaksihdr.Fields.Salesman_Name] == DBNull.Value ? string.Empty : dr[transaksihdr.Fields.Salesman_Name].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public object SelectScalar(SQL.Function.Aggregate function, string column,string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                       
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT(A.NO) FROM (SELECT COUNT({0}) NO FROM dbo.transaksihdr WHERE [Salesman Code] IS NOT NULL AND [Salesman Name] IS NOT NULL {1} GROUP BY [Salesman Code],[Salesman Name]) A", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }
    }
}
